﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for Requests
/// </summary>
public class Requests
{
	public Requests()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public int requestId { set; get; }
    public int accountId { set; get; }
    public int custId { set; get; }
    public string policyNo { set; get; }
    public decimal totalAmount { set; get; }
    public string metRepresentative { set; get; }
    public string requestType { set; get; }
    public DateTime requestTime { set; get; }
    public string status { set; get; }
    public string fundCode { set; get; }
    public decimal percentage { set; get; }
    public decimal amount { set; get; }
    public string type { set; get; }
    public string reason { set; get; }
    public string docPath { set; get; }
    public List<RequestsFund> requestFunds = new List<RequestsFund>(); 


    public void fundSwitchRedirectRequest(Dictionary<string, string> sourcePercentageOrAmount, Dictionary<string, string> targetPercentage, Dictionary<string, string> allocationPercentage)
    {
        string sql = "INSERT R_REQUEST (POLICY_NO,TOTAL_AMOUNT,MET_REPRESENTATIVE, REQUEST_TYPE,REQUEST_TIME, STATUS)" +
                      "VALUES ( " +
                      "@policyNo," +
                      "@totalAmount," +
                      "@metRepresentative," +
                      "@requestType," +
                      "@requestTime," +
                      "@status" +
                      ")SELECT SCOPE_IDENTITY()";

        decimal zero = 0m;
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@policyNo", policyNo);
        cmd.Parameters.AddWithValue("@totalAmount", zero);
        cmd.Parameters.AddWithValue("@metRepresentative", metRepresentative);
        cmd.Parameters.AddWithValue("@requestType", "SWITCH_REDIRECT");
        cmd.Parameters.AddWithValue("@requestTime", DateTime.Now);
        cmd.Parameters.AddWithValue("@status", "PENDING");
       
        object newId = cmd.ExecuteScalar();
        this.requestId = int.Parse(((Decimal)newId).ToString());


        foreach (String code in sourcePercentageOrAmount.Keys)
        {

            sql = "INSERT R_REQUEST_FUND (POLICY_NO,REQUEST_ID, FUND_CODE, PERCENTAGE,AMOUNT, TYPE)" +
                    "VALUES ( " +
                    "@policyNo," +
                    "@requestId," +
                    "@fundCode," +
                    "@percentage," +
                    "@amount," +
                    "@type" +
                    ")";
            cmd = new SqlCommand(sql, conn);

           
            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@requestId", requestId);
            cmd.Parameters.AddWithValue("@fundCode", code);
            
            cmd.Parameters.AddWithValue("@type", "SWITCH_FROM");

            
            string userInputValue = sourcePercentageOrAmount[code];
            if (userInputValue.StartsWith("$"))
            {
                decimal userInputAmount = Convert.ToDecimal(userInputValue.Substring(1));
                cmd.Parameters.AddWithValue("@amount", userInputAmount);
                cmd.Parameters.AddWithValue("@percentage", zero);
               
            }
            else
            {
                //Sell units from choosen fund according to the percentage to switch decided by policyholder
                decimal userInputPercentage = Convert.ToDecimal(userInputValue.Replace("%", "")) / 100;
                cmd.Parameters.AddWithValue("@amount", zero);
                cmd.Parameters.AddWithValue("@percentage", userInputPercentage);
            }

            cmd.ExecuteNonQuery();
        }

        foreach (String code in targetPercentage.Keys)
        {
           
            string userInputValue = targetPercentage[code];
            decimal userInputPercentage = Convert.ToDecimal(userInputValue) / 100;
         
            sql = "INSERT R_REQUEST_FUND (POLICY_NO,REQUEST_ID, FUND_CODE, PERCENTAGE,AMOUNT, TYPE)" +
                   "VALUES ( " +
                   "@policyNo," +
                   "@requestId," +
                   "@fundCode," +
                   "@percentage," +
                   "@amount," +
                   "@type" +
                   ")";
            cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@requestId", requestId);
            cmd.Parameters.AddWithValue("@fundCode", code);
            cmd.Parameters.AddWithValue("@type", "SWITCH_TARGET");
            cmd.Parameters.AddWithValue("@amount", zero);
            cmd.Parameters.AddWithValue("@percentage", userInputPercentage);
            cmd.ExecuteNonQuery();

        }
        
        
        foreach (String code in allocationPercentage.Keys)
        {
            
            string userInputValue = allocationPercentage[code];
            decimal userInputDecimal = Convert.ToDecimal(userInputValue)/100;

            sql = "INSERT R_REQUEST_FUND (POLICY_NO,REQUEST_ID, FUND_CODE, PERCENTAGE,AMOUNT, TYPE)" +
                   "VALUES ( " +
                   "@policyNo," +
                   "@requestId," +
                   "@fundCode," +
                   "@percentage," +
                   "@amount," +
                   "@type" +
                   ")";
            cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@requestId", requestId);
            cmd.Parameters.AddWithValue("@fundCode", code);
            cmd.Parameters.AddWithValue("@type", "REDIRECT_TARGET");
            cmd.Parameters.AddWithValue("@amount", zero);
            cmd.Parameters.AddWithValue("@percentage", userInputDecimal);
            cmd.ExecuteNonQuery();
        }
            conn.Close();
    }

    public static Requests getRequest2(int id)
    {

        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        String sql = " SELECT A.request_id, A.policy_no ,P.user_id, A.total_amount, " +
                     "   A.met_representative, " +
                     "   A.request_type,  " +
                     "   A.request_time,  " +
                     "   A.status, A.accountID, " +
                     "   B.fund_code,  " +
                     "   B.percentage,  " +
                     "   B.amount, B.type " +
                     " FROM R_REQUEST A, R_REQUEST_FUND B, R_POLICY P" +
                     " WHERE A.REQUEST_ID=B.REQUEST_ID AND" +
                     " P.policy_no=A.policy_no AND" +
                     " A.REQUEST_ID=" + id;
                     
        var cmd = new SqlCommand(sql, conn);
        SqlDataReader reader = cmd.ExecuteReader();
        Requests req = new Requests();
        while(reader.Read())
        {
            req.requestId = reader.GetInt32(0);
            req.policyNo = reader.GetString(1);
            req.custId = reader.GetInt32(2);
            req.totalAmount = reader.GetDecimal(3);
            req.metRepresentative = reader.GetString(4);
            req.requestType = reader.GetString(5);
            req.requestTime = reader.GetDateTime(6);
            req.status = reader.GetString(7);
            //req.accountId = reader.GetInt32(8);

            RequestsFund requestsFund = new RequestsFund();
            requestsFund.code = reader.GetString(9);
            requestsFund.requestId = reader.GetInt32(0);
            requestsFund.percentage = reader.GetDecimal(10);
            requestsFund.amount = reader.GetDecimal(11);
            requestsFund.type = reader.GetString(12);
            req.requestFunds.Add(requestsFund);
        }
        conn.Close();
        return req;

    }

    public DataSet getRequest(int id)
    {

        SqlConnection con = DBManager.getSqlConnection();

        String sql = " SELECT A.request_id, A.policy_no , A.total_amount, " +
                     "   A.met_representative, " +
                     "   A.request_type,  " +
                     "   A.request_time,  " +
                     "   A.status, A.accountID, " +
                     "   B.fund_code,  " +
                     "   B.percentage,  " +
                     "   B.amount, B.type " +
                     " FROM R_REQUEST A, R_REQUEST_FUND B " +
                     " WHERE A.REQUEST_ID=B.REQUEST_ID AND" +
                     " A.REQUEST_ID=" + id;

        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
        DataSet myDs = new DataSet();

        con.Open();
        myAdapter.Fill(myDs, "request");

        con.Close();
        return myDs;

    }

    public void fundTopUpRequest(Dictionary<string, string> targetPercentage)
    {
        string sql = "INSERT R_REQUEST (POLICY_NO,TOTAL_AMOUNT,MET_REPRESENTATIVE, REQUEST_TYPE,REQUEST_TIME, STATUS)" +
                      "VALUES ( " +
                      "@policyNo," +
                      "@totalAmount," +
                      "@metRepresentative," +
                      "@requestType," +
                      "@requestTime," +
                      "@status" +
                      ")SELECT SCOPE_IDENTITY()";

        decimal zero = 0m;
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@policyNo", policyNo);
        cmd.Parameters.AddWithValue("@totalAmount", totalAmount);
        cmd.Parameters.AddWithValue("@metRepresentative", metRepresentative);
        cmd.Parameters.AddWithValue("@requestType", "PREMIUM_TOP_UP");
        cmd.Parameters.AddWithValue("@requestTime", DateTime.Now);
        cmd.Parameters.AddWithValue("@status", "PENDING");

        object newId = cmd.ExecuteScalar();
        this.requestId = int.Parse(((Decimal)newId).ToString());


        foreach (String code in targetPercentage.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            string userInputValue = targetPercentage[code];
            decimal userInputPercentage = Convert.ToDecimal(userInputValue) / 100;

            sql = "INSERT R_REQUEST_FUND (POLICY_NO,REQUEST_ID, FUND_CODE, PERCENTAGE,AMOUNT, TYPE)" +
                   "VALUES ( " +
                   "@policyNo," +
                   "@requestId," +
                   "@fundCode," +
                   "@percentage," +
                   "@amount," +
                   "@type" +
                   ")";
            cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@requestId", requestId);
            cmd.Parameters.AddWithValue("@fundCode", fund.code);
            cmd.Parameters.AddWithValue("@type", "TOP_UP_TARGET");
            cmd.Parameters.AddWithValue("@amount", zero);
            cmd.Parameters.AddWithValue("@percentage", userInputPercentage);
            cmd.ExecuteNonQuery();
        }
            conn.Close();
    }

    public void fundWithdrawalRequest(Dictionary<string, string> targetPercentageOrAmount)
    {
        string sql = "INSERT R_REQUEST (POLICY_NO,TOTAL_AMOUNT,MET_REPRESENTATIVE, REQUEST_TYPE,REQUEST_TIME, STATUS, REASON)" +
                      "VALUES ( " +
                      "@policyNo," +
                      "@totalAmount," +
                      "@metRepresentative," +
                      "@requestType," +
                      "@requestTime," +
                      "@status," +
                      "@reason" +
                      ")SELECT SCOPE_IDENTITY()";

        decimal zero = 0m;
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@policyNo", policyNo);
        cmd.Parameters.AddWithValue("@totalAmount", zero);
        cmd.Parameters.AddWithValue("@metRepresentative", metRepresentative);
        cmd.Parameters.AddWithValue("@requestType", "WITHDRAWAL_TERMINATE");
        cmd.Parameters.AddWithValue("@requestTime", DateTime.Now);
        cmd.Parameters.AddWithValue("@status", "PENDING");
        cmd.Parameters.AddWithValue("@reason", reason);

        object newId = cmd.ExecuteScalar();
        this.requestId = int.Parse(((Decimal)newId).ToString());


        foreach (String code in targetPercentageOrAmount.Keys)
        {
            Fund fund = new Fund();
            fund.load(policyNo, code);

            sql = "INSERT R_REQUEST_FUND (POLICY_NO,REQUEST_ID, FUND_CODE, PERCENTAGE,AMOUNT, TYPE)" +
                    "VALUES ( " +
                    "@policyNo," +
                    "@requestId," +
                    "@fundCode," +
                    "@percentage," +
                    "@amount," +
                    "@type" +
                    ")";
            cmd = new SqlCommand(sql, conn);


            cmd.Parameters.AddWithValue("@policyNo", policyNo);
            cmd.Parameters.AddWithValue("@requestId", requestId);
            cmd.Parameters.AddWithValue("@fundCode", fund.code);

            cmd.Parameters.AddWithValue("@type", "WITHDRAWAL");


            string userInputValue = targetPercentageOrAmount[code];
            if (userInputValue.StartsWith("$"))
            {
                decimal userInputAmount = Convert.ToDecimal(userInputValue.Substring(1));
                cmd.Parameters.AddWithValue("@amount", userInputAmount);
                cmd.Parameters.AddWithValue("@percentage", zero);

            }
            else
            {
                //Sell units from choosen fund according to the percentage to withdrawal decided by policyholder
                decimal userInputPercentage = Convert.ToDecimal(userInputValue.Replace("%", "")) / 100;
                cmd.Parameters.AddWithValue("@amount", zero);
                cmd.Parameters.AddWithValue("@percentage", userInputPercentage);
            }

            cmd.ExecuteNonQuery();
            }
            conn.Close();
        }

    public void reject(int requestId, String reason){

            SqlConnection conn = DBManager.getSqlConnection();
            conn.Open();
            String sql = "UPDATE R_REQUEST "
                     + "SET status='REJECTED',reason='"+reason+"'"

                     + " WHERE requestId =" + requestId;
            
            SqlCommand cmd = new SqlCommand(sql,conn);
            cmd.ExecuteNonQuery();
            conn.Close();

            Requests req = Requests.getRequest2(requestId);
            String requestType = "";
            String requestUrl = "";
            if ("SWITCH_REDIRECT".Equals(req.requestType))
            {
                requestType = "Fund Switch / Premium Rediection ";
                requestUrl = "reviewFundSwitch.aspx?requestId=" + requestId;
            }
            else if ("PREMIUM_TOP_UP".Equals(req.requestType))
            {
                requestType = "Premium Top Up";
                requestUrl = "reviewPremiumTopUp.aspx?requestId=" + requestId;
            }
            else if ("WITHDRAWAL".Equals(req.requestType))
            {
                requestType = "Withdrawal / Terminate";
                requestUrl = "reviewWithdrawalTerminate.aspx?requestId=" + requestId;
            }
            else if ("TERMINATE".Equals(req.requestType))
            {
                requestType = "Withdrawal / Terminate";
                requestUrl = "reviewWithdrawalTerminate.aspx?requestId=" + requestId;
            }
            else if ("CHANGE_DETAIL".Equals(req.requestType))
            {
                requestType = "Change Details";
                requestUrl = "reviewChangeDetails.aspx?requestId=" + requestId;
            }

            Alert.sendAlert(req.custId, requestType, reason, requestUrl);
        }

    public void approve(int requestId)
    {

        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();
        String sql = "UPDATE R_REQUEST "
                 + "SET status='APPROVED',response_time='" + DateTime.Now + "'"

                 + " WHERE request_id =" + requestId;

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
		
        conn.Close();

        Requests req = Requests.getRequest2(requestId);
		
		foreach(RequestsFund requestsFund in req.requestFunds){
            if ("SWITCH_TARGET".Equals(requestsFund.type) || "REDIRECT_TARGET".Equals(requestsFund.type))
            {
				Fund fund = new Fund();
				fund.load(req.policyNo, requestsFund.code);
				if (Util.IsBlank(fund.code))
				{
					Fund.addNewFund(req.policyNo, requestsFund.code);
					fund.load(req.policyNo, requestsFund.code);
				}
			}
		}
		
        String requestType = "";
        String requestUrl = "";
        
        if ("SWITCH_REDIRECT".Equals(req.requestType))
        {
            requestType = "Fund Switch / Premium Rediection ";
            requestUrl = "reviewFundSwitch.aspx?requestId=" + requestId;
        }
        else if ("PREMIUM_TOP_UP".Equals(req.requestType))
        {
            requestType = "Premium Top Up";
            requestUrl = "reviewPremiumTopUp.aspx?requestId=" + requestId;
        }
        else if ("WITHDRAWAL".Equals(req.requestType))
        {
            requestType = "Withdrawal / Terminate";
            requestUrl = "reviewWithdrawalTerminate.aspx?requestId=" + requestId;
        }
        else if ("TERMINATE".Equals(req.requestType))
        {
            requestType = "Withdrawal / Terminate";
            requestUrl = "reviewWithdrawalTerminate.aspx?requestId=" + requestId;
        }
        else if ("CHANGE_DETAIL".Equals(req.requestType))
        {
            requestType = "Change Details";
            requestUrl = "reviewChangeDetails.aspx?requestId=" + requestId;
        }

        Alert.sendAlert(req.custId, requestType, "Request has been approved", requestUrl);
        }
    }


